# encoding: utf-8
'''
Created on 2018年6月15日
description:数据收集器
@author: zhujianhua
'''
import requests
import time
import json
import sys
sys.path.append('./../helper')
import InfoMonConstants
from LoggerHelper import LoggerHelper
from ConnectionHelper import ConnectionHelper


class Collector:
    def __init__(self):
        self.topSQL = {}
        self.uploadData = []
        self.ts = int(time.time())  # 时间戳
        self.logger = LoggerHelper("Collector")
        self.logger.info('collecting infomation from ' + InfoMonConstants.HOST)

    def run(self):
        self.initConnectionAndRun(self.collect)
        self.send(InfoMonConstants.FALCON_CLINENT, self.uploadData)

    def runTopSQL(self):
        self.initConnectionAndRun(self.getTopSQL)
        self.send(InfoMonConstants.EXTEND_CLIENT + '/top_sql', self.topSQL)

    # 初始化数据库连接和收集数据
    def initConnectionAndRun(self, fun):
        try:
            self.connHelper = ConnectionHelper(InfoMonConstants.SID)
        except Exception as e:
            self.logger.error(str(e))
            self.send(InfoMonConstants.EXTEND_CLIENT + '/delayed_db', {
                "endpoint": InfoMonConstants.ENDPOINT,
                "timestamp": self.ts,
                "ip": InfoMonConstants.HOST,
                "sid": InfoMonConstants.SID,
                "delay": 1
            })
        else:
            fun()
            self.connHelper.conn.close()

    # 数据收集
    def collect(self):
        try:
            self.getDbCacheRatio()
            self.getLibraryCacheRatio()
            self.getSharePoolFree()
            self.getMemorySortRatio()
            self.getResourceLimit()
            self.getCpuUtilization()
            self.getCpuUsagePerSec()
            self.getDatabaseWaitTime()
            self.getHardParseCountPerSec()
            self.getSoftParseRatio()
            self.getPhysicalWriteBytesPerSec()
            self.getPhysicalReadBytesPerSec()
            self.getUserTransactionPerSec()
            self.getUserCallPerSec()
            self.getTablScans()
            self.getTablespaceUsage()
            self.getAsmSpaceUsage()
            self.getDatabaseStatus()
            self.getLongTransaction()
            self.getNetworkTraffic()
            self.getMemoryUsage()

            # self.logger.info(self.uploadData)
        except Exception as e:
            self.logger.error(str(e))

    # 收集top sql数据
    def getTopSQL(self):
        try:
            sqlID = []
            res = []
            sortList = ['sorts', 'executions', 'persistent_mem', 'DISK_READS', 'DIRECT_WRITES', 'BUFFER_GETS',
                        'USER_IO_WAIT_TIME', 'cpu_time', 'ELAPSED_TIME', 'PHYSICAL_READ_BYTES', 'PHYSICAL_WRITE_BYTES']
            for sortItem in sortList:
                self.logger.info("collect " + sortItem + " top sql...")
                sql = 'select rn,inst_id,sql_text,sql_id,persistent_mem,sorts,executions,FIRST_LOAD_TIME,DISK_READS, \
                DIRECT_WRITES,BUFFER_GETS,USER_IO_WAIT_TIME/1000 as user_io_wait_ms,CPU_TIME/1000 as cpu_time_ms,ELAPSED_TIME/1000 as ELAPSED_TIME_MS, \
                PHYSICAL_READ_BYTES/1024 as PHYSICAL_READ_BYTES_KB,PHYSICAL_WRITE_BYTES/1024 as PHYSICAL_WRITE_BYTES_KB \
                from (select row_number() over(partition by inst_id order by {} desc) rn, t.* from gv$sqlarea t) where rn < 21'.format(sortItem)
                results = self.connHelper.execute(sql)
                for line in results:
                    if line[3] not in sqlID:
                        sqlID.append(line[3])
                        res.append({
                            "sql_text": line[2],
                            "sql_id": line[3],
                            "persistent_mem": line[4],
                            "sorts": line[5],
                            "executions": line[6],
                            "disk_reads": line[8],
                            "direct_writes": line[9],
                            "buffer_gets": line[10],
                            "user_io_wait_ms": line[11],
                            "cpu_time_ms": line[12],
                            "elapsed_time_ms": line[13],
                            "physical_read_bytes_kb": line[14],
                            "physical_write_bytes_kb": line[15]
                        })
                self.logger.info("collect " + sortItem + " top sql finish")
            self.topSQL = {
                "endpoint": InfoMonConstants.ENDPOINT,
                "sid": InfoMonConstants.SID,
                "metric": "top_sql",
                "timestamp": self.ts,
                "step": 60,
                "values": res,
                "counterType": "GAUGE",
                "tags": ""
            }
            # self.logger.info(self.topSQL)
        except Exception as e:
            self.logger.error(str(e))

    # 发送数据到transfer
    def send(self, url, data):
        if 0 != len(data):
            self.logger.info("sending data to " + url)
            try:
                r = requests.post(url, data=json.dumps(data))
                self.logger.info(r.text)
            except requests.exceptions.ConnectionError:
                self.logger.error('failed to connect to ' + url)
                self.logger.error('transfer data failed')

    # db缓存命中率
    def getDbCacheRatio(self):
        self.logger.info("collect db cache ratio...")
        results = self.connHelper.execute('SELECT NAME,PHYSICAL_READS,DB_BLOCK_GETS,CONSISTENT_GETS,1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio" FROM V$BUFFER_POOL_STATISTICS WHERE NAME = \'DEFAULT\'')
        for line in results:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "db_cache_ratio_physical_reads",
                "timestamp": self.ts,
                "step": 60,
                "value": line[1],
                "counterType": "GAUGE",
                "tags": "name=" + line[0],
            })
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "db_cache_ratio_db_block_gets",
                "timestamp": self.ts,
                "step": 60,
                "value": line[2],
                "counterType": "GAUGE",
                "tags": "name=" + line[0],
            })
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "db_cache_ratio_consistent_gets",
                "timestamp": self.ts,
                "step": 60,
                "value": line[3],
                "counterType": "GAUGE",
                "tags": "name=" + line[0],
            })
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "db_cache_ratio_hit_ratio",
                "timestamp": self.ts,
                "step": 60,
                "value": line[4],
                "counterType": "GAUGE",
                "tags": "name=" + line[0],
            })
        self.logger.info("collect db cache ratio finish")

    # 库缓存命中率
    def getLibraryCacheRatio(self):
        self.logger.info("collect library  cache ratio...")
        results = self.connHelper.execute('SELECT SUM(pinhits) / sum(pins) FROM V$LIBRARYCACHE')
        for line in results:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "library_cahce_ratio",
                "timestamp": self.ts,
                "step": 60,
                "value": line[0],
                "counterType": "GAUGE",
                "tags": "",
            })
        self.logger.info("collect library  cache ratio finish")

    # 共享池剩余量
    def getSharePoolFree(self):
        self.logger.info("collect share pool free...")
        results = self.connHelper.execute('SELECT * FROM V$SGASTAT WHERE NAME = \'free memory\' AND POOL = \'shared pool\'')
        for line in results:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "share_pool_free",
                "timestamp": self.ts,
                "step": 60,
                "value": line[2],
                "counterType": "GAUGE",
                "tags": "type=bytes",
            })
        self.logger.info("collect share pool free finish")

    # 内存排序比率
    def getMemorySortRatio(self):
        self.logger.info("collect memory sort ratio...")
        results = self.connHelper.execute('SELECT round((SELECT value FROM v$sysstat t WHERE NAME = \'sorts (memory)\') /((SELECT value FROM v$sysstat t WHERE NAME = \'sorts (memory)\') +(SELECT value FROM v$sysstat t WHERE NAME = \'sorts (disk)\')),4) * 100  FROM dual')
        for line in results:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "memory_sort_ratio",
                "timestamp": self.ts,
                "step": 60,
                "value": line[0],
                "counterType": "GAUGE",
                "tags": "",
            })
        self.logger.info("collect memory sort ratio finish")

    # 资源限制情况收集
    def getResourceLimit(self):
        self.logger.info("collect resource limit...")
        results = self.connHelper.execute('select * from gv$resource_limit where resource_name = \'processes\'')
        for line in results:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "resource_limit_current_utilization",
                "timestamp": self.ts,
                "step": 60,
                "value": line[2],
                "counterType": "GAUGE",
                "tags": "resource_name=processes,INST_ID=" + str(line[0]),
            })
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "resource_limit_max_utilization",
                "timestamp": self.ts,
                "step": 60,
                "value": line[3],
                "counterType": "GAUGE",
                "tags": "resource_name=processes,INST_ID=" + str(line[0]),
            })
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "resource_limit_initial_allocation",
                "timestamp": self.ts,
                "step": 60,
                "value": int(line[4]),
                "counterType": "GAUGE",
                "tags": "resource_name=processes,INST_ID=" + str(line[0]),
            })
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "resource_limit_limit_value",
                "timestamp": self.ts,
                "step": 60,
                "value": int(line[5]),
                "counterType": "GAUGE",
                "tags": "resource_name=processes,INST_ID=" + str(line[0]),
            })
        self.logger.info("collect resource limit finish")

    # cpu利用率
    def getCpuUtilization(self):
        self.logger.info("collect cpu utilization...")
        results = self.connHelper.execute('select * from gv$sysmetric where group_id = 2 and metric_name = \'Host CPU Utilization (%)\'')
        for line in results:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "host_CPU_utilization",
                "timestamp": self.ts,
                "step": 60,
                "value": line[7],
                "counterType": "GAUGE",
                "tags": "",
            })
        self.logger.info("collect cpu utilization finish")

    # cpu负载
    def getCpuUsagePerSec(self):
        self.logger.info("collect cpu usage PerSec...")
        results = self.connHelper.execute('select * from gv$sysmetric where group_id = 2 and metric_name = \'CPU Usage Per Sec\'')
        for line in results:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "CPU_usage_per_sec",
                "timestamp": self.ts,
                "step": 60,
                "value": format(float(line[7])/100, '.2f'), 
                "counterType": "GAUGE",
                "tags": "",
            })
        self.logger.info("collect cpu usage PerSec finish")

    # 数据库等待比例
    def getDatabaseWaitTime(self):
        self.logger.info("collect database wait time...")
        results = self.connHelper.execute('select * from gv$sysmetric where group_id = 2 and metric_name = \'Database Wait Time Ratio\'')
        for line in results:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "database_wait_time_ratio",
                "timestamp": self.ts,
                "step": 60,
                "value": line[7],
                "counterType": "GAUGE",
                "tags": "",
            })
        self.logger.info("collect database wait time finish")

    # 硬解析
    def getHardParseCountPerSec(self):
        self.logger.info("collect hard parse count PerSec...")
        results = self.connHelper.execute('select * from gv$sysmetric where group_id = 2 and metric_name = \'Hard Parse Count Per Sec\'')
        for line in results:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "hard_parse_count_per_sec",
                "timestamp": self.ts,
                "step": 60,
                "value": line[7],
                "counterType": "GAUGE",
                "tags": "",
            })
        self.logger.info("collect hard parse count PerSec finish")

    # 软解析比例
    def getSoftParseRatio(self):
        self.logger.info("collect soft parse ratio...")
        results = self.connHelper.execute('select * from gv$sysmetric where group_id = 2 and metric_name = \'Soft Parse Ratio\'')
        for line in results:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "soft_parse_ratio",
                "timestamp": self.ts,
                "step": 60,
                "value": line[7],
                "counterType": "GAUGE",
                "tags": "",
            })
        self.logger.info("collect soft parse ratio finish")

    # 物理写比例
    def getPhysicalWriteBytesPerSec(self):
        self.logger.info("collect physical write bytes PerSec...")
        results = self.connHelper.execute('select * from gv$sysmetric where group_id = 2 and metric_name = \'Physical Write Bytes Per Sec\'')
        for line in results:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "physical_write_bytes_per_sec",
                "timestamp": self.ts,
                "step": 60,
                "value": line[7],
                "counterType": "GAUGE",
                "tags": "",
            })
        self.logger.info("collect physical write bytes PerSec finish")

    # 物理读比例
    def getPhysicalReadBytesPerSec(self):
        self.logger.info("collect physical read bytes PerSec...")
        results = self.connHelper.execute('select * from gv$sysmetric where group_id = 2 and metric_name = \'Physical Read Bytes Per Sec\'')
        for line in results:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "physical_read_bytes_per_sec",
                "timestamp": self.ts,
                "step": 60,
                "value": line[7],
                "counterType": "GAUGE",
                "tags": "",
            })
        self.logger.info("collect physical read bytes PerSec finish")

    # 每秒事物数量
    def getUserTransactionPerSec(self):
        self.logger.info("collect user transaction PerSec...")
        results = self.connHelper.execute('select * from gv$sysmetric where group_id = 2 and metric_name = \'User Transaction Per Sec\'')
        for line in results:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "user_transaction_per_sec",
                "timestamp": self.ts,
                "step": 60,
                "value": line[7],
                "counterType": "GAUGE",
                "tags": "",
            })
        self.logger.info("collect user transaction PerSec finish")

    # 用户使用量
    def getUserCallPerSec(self):
        self.logger.info("collect user call PerSec...")
        results = self.connHelper.execute('select * from gv$sysmetric where group_id = 2 and metric_name = \'User Calls Per Sec\'')
        for line in results:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "user_calls_per_sec",
                "timestamp": self.ts,
                "step": 60,
                "value": line[7],
                "counterType": "GAUGE",
                "tags": "",
            })
        self.logger.info("collect user call PerSec finish")

    # 大小表扫描
    def getTablScans(self):
        self.logger.info("collect table scans...")
        results = self.connHelper.execute('select name,value from v$sysstat where name in (\'table scans (short tables)\',\'table scans (long tables\')')
        for line in results:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "table_scans",
                "timestamp": self.ts,
                "step": 60,
                "value": line[1],
                "counterType": "GAUGE",
                "tags": line[0],
            })
        self.logger.info("collect table scans finish")

        # 表空间使用情况

    def getTablespaceUsage(self):
        self.logger.info("collect tablespace usage...")
        results = self.connHelper.execute('select a.tablespace_name,a.tablespace_size * b.block_size / 1024 / 1024/1024 "totalmsize(G)",a.used_space * b.block_size / 1024 / 1024/1024 "usedmsize(g)",round(a.used_percent, 2) "usedrate(%)"\
            from dba_tablespace_usage_metrics a, dba_tablespaces b\
            where a.tablespace_name = b.tablespace_name')
        for line in results:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "tablespace_" + line[0] + "_totalmsize",
                "timestamp": self.ts,
                "step": 60,
                "value": line[1],
                "counterType": "GAUGE",
                "tags": "",
            })
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "tablespace_" + line[0] + "_usedmsize",
                "timestamp": self.ts,
                "step": 60,
                "value": line[2],
                "counterType": "GAUGE",
                "tags": "",
            })
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "tablespace_" + line[0] + "_usedrate",
                "timestamp": self.ts,
                "step": 60,
                "value": line[3],
                "counterType": "GAUGE",
                "tags": "%",
            })
        self.logger.info("collect tablespace usage finish")

    def getAsmSpaceUsage(self):
        results = self.connHelper.execute('select * from v$asm_client')
        if results:
            # 通过asm文件管理
            asmsql = "select name,round(total_mb/1024),round(free_mb/1024),1-round((free_mb/total_mb),3) as usage from v$asm_diskgroup"
            asmdisk = self.connHelper.execute(asmsql)
            for line in asmdisk:
                self.uploadData.append({
                    "endpoint": InfoMonConstants.ENDPOINT,
                    "metric": "AsmUsage_" + line[0] + "_total_Gb",
                    "timestamp": self.ts,
                    "step": 60,
                    "value": line[1],
                    "counterType": "GAUGE",
                    "tags": "name=asmdisk_usage",
                })
                self.uploadData.append({
                    "endpoint": InfoMonConstants.ENDPOINT,
                    "metric": "AsmUsage_" + line[0] + "_free_Gb",
                    "timestamp": self.ts,
                    "step": 60,
                    "value": line[2],
                    "counterType": "GAUGE",
                    "tags": "name=asmdisk_usage",
                })
                self.uploadData.append({
                    "endpoint": InfoMonConstants.ENDPOINT,
                    "metric": "AsmUsage_" + line[0] + "_UsagePct",
                    "timestamp": self.ts,
                    "step": 60,
                    "value": line[3],
                    "counterType": "GAUGE",
                    "tags": "name=asmdisk_usage",
                })
        else:
            # 不是通过asm管理，文件系统方式
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "AsmUsage",
                "timestamp": self.ts,
                "step": 60,
                "value": 0,
                "counterType": "GAUGE",
                "tags": "",
            })

    def getDatabaseStatus(self):
        results = self.connHelper.execute('select inst_id,instance_name,status from gv$instance')
        for line in results:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "DatabaseStatus_"+line[1],
                "timestamp": self.ts,
                "step": 60,
                "value": line[2],
                "counterType": "GAUGE",
                "tags": "",
            })

    def getLongTransaction(self):
        longtime = 1
        sql = "select count(*) \
        from gv$session a, gv$process b\
        where a.paddr=b.addr\
        and ((a.event not like '%SQL%' and a.event not like '%message%') or a.event ='SQL*Net message from dblink')\
        and a.event not like '%time%'\
        and a.event not like 'PX Deq:%'\
        and a.event not like 'jobq slave%'\
        and a.event not like '%slave idle%'\
        and a.event not like 'Stream%'\
        and a.event <> 'class slave wait'\
        and a.sql_id is not null and decode(wait_time,0,SECONDS_IN_WAIT,0) > {}".format(longtime)
        result = self.connHelper.execute(sql)
        for line in result:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "LongTransaction_count",
                "timestamp": self.ts,
                "step": 60,
                "value": line[0],
                "counterType": "GAUGE",
                "tags": "",
            })
    
    #网络传输        
    def getNetworkTraffic(self):
        sql ="select * from gv$metric where metric_name = 'Network Traffic Volume Per Sec' and group_id = 2"
        results = self.connHelper.execute(sql)
        for line in results:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "network_traffic_bytes_per_sec",
                "timestamp": self.ts,
                "step": 60,
                "value": line[9],
                "counterType": "GAUGE",
                "tags": ""
            })
            
    #获取内存使用率
    def getMemoryUsage(self):
        momorySql = "select t1.stat_name,t1.value/1024/1024, t2.NAME,t2.used, round((t2.used)/(t1.value/1024/1024),2) as usage from v$osstat t1, (select 'TOTAL' AS NAME,\
                ((SELECT ROUND(sum(value) / 1024 / 1024, 2) from v$sga) +\
                (select ROUND(value / 1024 / 1024, 2) from v$pgastat\
                where name = 'total PGA allocated')) AS used\
                FROM DUAL ) t2\
                where t1.STAT_NAME = 'PHYSICAL_MEMORY_BYTES'"
                
        results = self.connHelper.execute(momorySql)
        for line in results:
            self.uploadData.append({
                "endpoint": InfoMonConstants.ENDPOINT,
                "metric": "memory_total_usage",
                "timestamp": self.ts,
                "step": 60,
                "value": line[4] * 100,
                "counterType": "GAUGE",
                "tags": ""
            })          
